package com.vehiclemanagement.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.vehiclemanagement.entity.ETCPassage;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * ETC通行记录Mapper接口
 */
@Mapper
public interface ETCPassageMapper extends BaseMapper<ETCPassage> {
    
    /**
     * 自定义分页查询，带关联信息
     * 
     * @param page 分页对象
     * @param whereSql 自定义WHERE条件SQL（不包含WHERE关键字）
     * @return 分页结果
     */
    @Select("SELECT p.*, v.plate_number, e.card_number as etc_card_number, " +
            "CASE p.passage_type " +
            "WHEN 1 THEN '高速公路' " +
            "WHEN 2 THEN '城市快速路' " +
            "WHEN 3 THEN '其他' " +
            "ELSE '未知' END as passage_type_name, " +
            "CASE p.status " +
            "WHEN 1 THEN '已创建' " +
            "WHEN 2 THEN '已结算' " +
            "WHEN 3 THEN '异常' " +
            "ELSE '未知' END as status_name " +
            "FROM etc_passage p " +
            "LEFT JOIN vehicle v ON p.vehicle_id = v.id " +
            "LEFT JOIN etc_card e ON p.etc_card_id = e.id " +
            "WHERE ${whereSql}")
    IPage<ETCPassage> selectPageWithInfo(Page<ETCPassage> page, @Param("whereSql") String whereSql);

    /**
     * 统计某辆车的总通行费用
     * 
     * @param vehicleId 车辆ID
     * @return 总通行费用
     */
    @Select("SELECT COALESCE(SUM(fee), 0) FROM etc_passage WHERE vehicle_id = #{vehicleId} AND del_flag = 0")
    BigDecimal sumFeeByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 统计某张ETC卡的总通行费用
     * 
     * @param etcCardId ETC卡ID
     * @return 总通行费用
     */
    @Select("SELECT COALESCE(SUM(fee), 0) FROM etc_passage WHERE etc_card_id = #{etcCardId} AND del_flag = 0")
    BigDecimal sumFeeByEtcCardId(@Param("etcCardId") Long etcCardId);

    /**
     * 月度通行费用统计
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 月度统计数据
     */
    @Select("SELECT DATE_FORMAT(exit_time, '%Y-%m') as month, " +
            "COUNT(*) as passage_count, " +
            "SUM(fee) as total_fee, " +
            "SUM(distance) as total_distance, " +
            "COUNT(DISTINCT vehicle_id) as vehicle_count " +
            "FROM etc_passage " +
            "WHERE exit_time BETWEEN #{startTime} AND #{endTime} AND del_flag = 0 " +
            "GROUP BY DATE_FORMAT(exit_time, '%Y-%m') " +
            "ORDER BY month")
    List<Map<String, Object>> statisticsByMonth(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 通行类型统计
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 通行类型统计数据
     */
    @Select("SELECT passage_type, " +
            "COUNT(*) as passage_count, " +
            "SUM(fee) as total_fee, " +
            "SUM(distance) as total_distance " +
            "FROM etc_passage " +
            "WHERE exit_time BETWEEN #{startTime} AND #{endTime} AND del_flag = 0 " +
            "GROUP BY passage_type")
    List<Map<String, Object>> statisticsByPassageType(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 物理删除ETC通行记录
     * 
     * @param id 记录ID
     * @return 影响行数
     */
    @Delete("DELETE FROM etc_passage WHERE id = #{id}")
    int deletePhysically(@Param("id") Long id);

    /**
     * 根据驾驶员ID获取总通行费用
     *
     * @param driverId 驾驶员ID
     * @return 总通行费用
     */
    BigDecimal sumFeeByDriverId(@Param("driverId") Long driverId);

    /**
     * 按年月统计ETC通行费用
     *
     * @param year  年份
     * @param month 月份（可选）
     * @return 统计数据
     */
    @Select("<script>" +
            "SELECT " +
            "<choose>" +
            "  <when test='month != null'>" +
            "    CONCAT(#{year}, '-', LPAD(#{month}, 2, '0')) as time_period" +
            "  </when>" +
            "  <otherwise>" +
            "    CONCAT(#{year}, '-', LPAD(MONTH(passage_time), 2, '0')) as time_period" +
            "  </otherwise>" +
            "</choose>" +
            ", COUNT(*) as record_count" +
            ", SUM(fee) as total_fee" +
            ", AVG(fee) as avg_fee" +
            ", COUNT(DISTINCT vehicle_id) as vehicle_count " +
            "FROM etc_passage " +
            "WHERE YEAR(passage_time) = #{year} " +
            "<if test='month != null'>" +
            "  AND MONTH(passage_time) = #{month} " +
            "</if>" +
            "AND status = 1 " +
            "AND del_flag = 0 " +
            "<if test='month == null'>" +
            "GROUP BY YEAR(passage_time), MONTH(passage_time) " +
            "ORDER BY time_period" +
            "</if>" +
            "</script>")
    List<Map<String, Object>> getETCPassageStatistics(@Param("year") Integer year, @Param("month") Integer month);
} 